Imports System.Xml
Public Class Command
    Implements IDbCommand


    Private _CommandType As System.Data.CommandType
    Private _CommandText As String
    Private _Connection As Connection


    Private _Parameters As DataParameterCollection
    Private _Transaction As Transaction
    Public Sub Cancel() Implements System.Data.IDbCommand.Cancel

    End Sub

    Public Property CommandText() As String Implements System.Data.IDbCommand.CommandText
        Get
            Return _CommandText
        End Get
        Set(ByVal value As String)
            _CommandText = value
        End Set
    End Property

    Public Property CommandTimeout() As Integer Implements System.Data.IDbCommand.CommandTimeout
        Get

        End Get
        Set(ByVal value As Integer)

        End Set
    End Property

    Public Property CommandType() As System.Data.CommandType Implements System.Data.IDbCommand.CommandType
        Get
            Return _CommandType
        End Get
        Set(ByVal value As System.Data.CommandType)
            Select Case value
                Case Data.CommandType.Text
                    _CommandType = value
                Case Else
                    Throw New NotSupportedException("Command Type must be Text!")
            End Select
        End Set
    End Property

    Public Property Connection() As System.Data.IDbConnection Implements System.Data.IDbCommand.Connection
        Get
            Return _Connection
        End Get
        Set(ByVal value As System.Data.IDbConnection)
            _Connection = value
        End Set
    End Property

    Private ReadOnly Property cn() As Connection
        Get
            Return CType(Me.Connection, Connection)
        End Get
    End Property

    Public Function CreateParameter() As System.Data.IDbDataParameter Implements System.Data.IDbCommand.CreateParameter
        Return New DataParameter()
    End Function

    Public Function ExecuteNonQuery() As Integer Implements System.Data.IDbCommand.ExecuteNonQuery
        Dim reader As New IO.BinaryReader(OpenEmbeddedResource("sql.cgt"))
        Dim grammar As New GoldParser.Grammar(reader)
        Dim Parser As IParser = New Parser()

        Dim update As New UpdateStatement(Parser.Parse(Me.CommandText, grammar))


        update.AssignList.Add(New AssignmentExpression("Body", "Test"))
        'First of all, Sharepoint only lets you update with the ID field
        Dim s As New SelectStatement
        s.WhereClause = update.WhereClause
        s.Columns.Add(New ColumnDefinition("ID"))

        Dim dr As System.Data.IDataReader = ExecuteReader()



        Throw New NotSupportedException()
    End Function

    Public Function ExecuteReader() As System.Data.IDataReader Implements System.Data.IDbCommand.ExecuteReader
        Return ExecuteReader(CommandBehavior.Default)
    End Function
    Public Function ExecuteReader(ByVal List As String, ByVal View As String, ByVal query As Xml.XmlNode, ByVal fields As Xml.XmlNode, ByVal RowLimit As Integer) As System.Data.IDataReader

    End Function

    Private Function GetResourceReader(ByVal resourceName As String) As IO.BinaryReader
        Dim assembly As Reflection.Assembly = Me.GetType().Assembly
        Dim stream As IO.Stream = assembly.GetManifestResourceStream(resourceName)
        Return New IO.BinaryReader(stream)
    End Function

    Private Function OpenEmbeddedResource(ByVal resourceName As String) As IO.Stream
        Return System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("System.Data.SharePointClient." & resourceName)
    End Function

    Public Function ExecuteReader(ByVal behavior As System.Data.CommandBehavior) As System.Data.IDataReader Implements System.Data.IDbCommand.ExecuteReader
        '	lists.Timeout = this.CommandTimeout;
        Try
            Dim reader As New IO.BinaryReader(OpenEmbeddedResource("sql.cgt"))
            Dim grammar As New GoldParser.Grammar(reader)
            Dim Parser As IParser = New Parser()

            Dim statement As New SelectStatement(Parser.Parse(Me.CommandText, grammar))

            Debug.Assert(statement IsNot Nothing)

            'Todo: Support more than one table
            Debug.Assert(statement.FromClause.Tables.Count = 1)
            Debug.Assert(statement.Columns.Count > 0 OrElse statement.Columns.IsWildCard = True)

            Return ExecuteReader_UsingWebService(behavior, statement)

        Catch ex As Exception
            Throw
        End Try

    End Function

    Private Function Fill(ByVal table As DataTable, ByVal TechListName As String, ByVal ViewName As String, ByVal DisplayNames As Hashtable, ByVal query As XmlNode, ByVal fields As XmlNode, ByVal rowLimit As Integer) As DataTable
        Try
            Dim ItemsNode As XmlNode

            If ViewName <> String.Empty Then
                ItemsNode = cn.Lists.GetListItems(TechListName, GetViewID(TechListName, ViewName), query, fields, rowLimit, Nothing, Nothing)
            Else
                ItemsNode = cn.Lists.GetListItems(TechListName, "", query, fields, rowLimit, Nothing, Nothing)
            End If


            '	// Lookup fields always start with the numeric ID, then ;# and then the string representation. 
            '	// We are normally only interested in the name, so we strip the ID.
            Dim CheckLookup As System.Text.RegularExpressions.Regex = New System.Text.RegularExpressions.Regex("^\\d+;#")

            For Each Item As XmlElement In ItemsNode.SelectNodes("rs:data/z:row", NameSpaceMgr)
                Dim newRow As Data.DataRow = table.NewRow()
                For Each col As Data.DataColumn In table.Columns
                    If Item.HasAttribute("ows_" & CType(DisplayNames.Item(col.ColumnName), String)) Then
                        Dim val As String = Item.GetAttribute("ows_" & CType(DisplayNames.Item(col.ColumnName), String))
                        If CheckLookup.IsMatch(val) Then
                            Dim valString As String = val
                            val = valString.Substring(valString.IndexOf("#") + 1)
                        End If
                        If val <> "" Then
                            Debug.Write(val)
                        End If
                        '				// Assigning a string to a field that expects numbers or 
                        '				// datetime values will implicitly convert them
                        Select Case col.DataType.ToString
                            Case GetType(DateTime).ToString
                                Dim d As DateTime
                                If DateTime.TryParse(val, d) = False Then
                                    newRow(col) = DBNull.Value
                                Else
                                    newRow(col) = val
                                End If
                            Case GetType(Integer).ToString
                                Dim i As Integer
                                If Integer.TryParse(val, i) = False Then
                                    newRow(col) = DBNull.Value
                                Else
                                    newRow(col) = val
                                End If
                            Case GetType(Decimal).ToString
                                Dim d As Decimal
                                If Decimal.TryParse(val, d) = False Then
                                    newRow(col) = DBNull.Value
                                Else
                                    newRow(col) = val
                                End If
                            Case Else
                                newRow(col) = val
                        End Select
                    End If
                Next
                table.Rows.Add(newRow)
            Next
            Return table
        Catch ex As System.Web.Services.Protocols.SoapException
            Throw
        Catch ex As Exception
            Throw
        End Try
    End Function

    Private Function ExecuteReader_UsingWebService(ByVal behavior As System.Data.CommandBehavior, ByVal statement As SelectStatement) As System.Data.IDataReader
        Try

            Dim result As Data.DataTable = New Data.DataTable("Table1")

            Dim Lists As System.Xml.XmlNode = cn.Lists.GetListCollection()

            For Each Table As Table In statement.FromClause.Tables

                Dim List As XmlElement

                List = Lists.SelectSingleNode(String.Format("wss:List[@Title='{0}']", Table.List), NameSpaceMgr)

                If List Is Nothing Then
                    Throw New ArgumentException()
                End If

                Dim ListInfoNode As XmlNode = cn.Lists.GetList(Table.List)


                Dim fieldRefs As System.Text.StringBuilder = New System.Text.StringBuilder()
                Dim TechListName As String = List.GetAttribute("Name")
                Dim DisplayNames As System.Collections.Hashtable = New System.Collections.Hashtable()

                Dim FieldNames As New Generic.Dictionary(Of String, XmlElement)

                For Each Field As XmlElement In ListInfoNode.SelectNodes("wss:Fields/wss:Field", NameSpaceMgr)
                    FieldNames.Add(Field.GetAttribute("Name"), Field)
                Next

                If statement.Columns.IsWildCard = False Then
                    For Each column As ColumnDefinition In statement.Columns.GetColumnsByTable(statement.FromClause.Tables, Table.List)
                        Dim FieldName As String = column.ColumnName.Name
                        Dim FieldDisplayName As String = String.Empty

                        If FieldNames.ContainsKey(FieldName) = False Then
                            Throw New Exception("Cannot Find field " & FieldName)
                        End If

                        FieldDisplayName = column.ColumnAlias
                        result.Columns.Add(FieldDisplayName, TypeFromField(FieldNames(FieldName)))
                       
                        fieldRefs.AppendFormat("<FieldRef Name=""{0}""/>", FieldName)

                        DisplayNames.Add(FieldDisplayName, FieldName)
                    Next
                Else
                    'All Columns
                    For Each field As XmlElement In FieldNames.Values
                        Dim FieldName As String = field.GetAttribute("Name")
                        Dim FieldDisplayName As String = field.GetAttribute("DisplayName")
                        If DisplayNames.ContainsKey(FieldDisplayName) Then
                            FieldDisplayName = field.GetAttribute("DisplayName") & "(" & FieldName & ")"
                        End If

                        DisplayNames.Add(FieldDisplayName, FieldName)
                        result.Columns.Add(FieldDisplayName, TypeFromField(FieldNames(FieldName)))
                        fieldRefs.AppendFormat("<FieldRef Name=""{0}""/>", FieldName)
                    Next
                End If

                If behavior = CommandBehavior.SchemaOnly Then
                    Return New DataReader(result, cn)
                End If


                Dim fields As XmlElement = ListInfoNode.OwnerDocument.CreateElement("ViewFields")
                fields.InnerXml = fieldRefs.ToString()

                Dim query As XmlNode = SelectStatementToCAML(statement, result)

                result = Fill(result, TechListName, Table.View, DisplayNames, query, fields, statement.RowLimit)
            Next

            Return New DataReader(result, cn)
        Catch ex As Exception
            Throw
        End Try

    End Function

    Private Function GetViewID(ByVal TechListName As String, ByVal ViewName As String) As String

        Dim node As Xml.XmlNode

        node = CType(Me.Connection, Connection).Views.GetViewCollection(TechListName)
        Dim n As XmlNode = node.SelectSingleNode(String.Format("wss:View[@DisplayName='{0}']", ViewName), NameSpaceMgr)
        If n IsNot Nothing Then
            Return n.Attributes("Name").Value
        Else
            Return ""
        End If
    End Function
    Private Function CreateXMLElement(ByVal name As String) As Xml.XmlElement
        Dim d As New XmlDocument()
        Return d.CreateElement(name)
    End Function

    Private Function SelectStatementToCAML(ByVal statement As SelectStatement, ByVal schema As DataTable) As Xml.XmlNode
        Dim doc As New XmlDocument()

        Dim node As XmlElement = doc.CreateElement("Query")

        'node.AppendChild(where)
        If statement.WhereClause.Expression IsNot Nothing Then
            Dim where As XmlElement = doc.CreateElement("Where")
            _ParameterIndex = 0
            where.AppendChild(ExpressionToCAML(statement.WhereClause.Expression, statement.Columns, schema, doc))
            node.AppendChild(where)
            _ParameterIndex = 0
        End If

        If statement.OrderClause.List.Count > 0 Then
            Dim OrderBy As XmlElement = doc.CreateElement("OrderBy")
            For Each order As OrderType In statement.OrderClause.List
                Dim field As XmlElement = doc.CreateElement("FieldRef")

                Dim name As XmlAttribute = doc.CreateAttribute("Name")
                name.Value = order.Column

                Dim ascending As XmlAttribute = doc.CreateAttribute("Ascending")
                If order.Ascending = True Then
                    ascending.Value = "TRUE"
                Else
                    ascending.Value = "FALSE"
                End If
                field.Attributes.Append(name)
                field.Attributes.Append(ascending)
                OrderBy.AppendChild(field)
            Next
            node.AppendChild(OrderBy)
        End If

        If statement.GroupClause.List.Count > 0 Then
            Dim GroupBy As XmlElement = doc.CreateElement("GroupBy")
            For Each field As String In statement.GroupClause.List
                Dim fieldRef As XmlElement = doc.CreateElement("FieldRef")
                Dim name As XmlAttribute = doc.CreateAttribute("Name")
                name.Value = field
                fieldRef.Attributes.Append(name)
                GroupBy.AppendChild(fieldRef)
            Next
            node.AppendChild(GroupBy)
        End If
        Return node
    End Function


    Private Function UpdateStatementToCAML(ByVal update As UpdateStatement) As Xml.XmlNode
        Dim doc As New Xml.XmlDocument()
        Dim node As Xml.XmlNode = doc.CreateElement("Method")

        For Each obj As AssignmentExpression In update.AssignList

        Next


    End Function

    Private Function ToSPFieldType(ByVal t As System.Type) As String
        Select Case t.ToString
            Case GetType(String).ToString
                Return "Text"
            Case GetType(Integer).ToString
                Return "Integer"
            Case GetType(DateTime).ToString
                Return "DateTime"
            Case GetType(Boolean).ToString
                Return "Boolean"
            Case Else
                Throw New NotSupportedException()
        End Select
    End Function
    Private _ParameterIndex As Integer
    Private Function ExpressionToCAML(ByVal e As BaseExpression, ByVal columns As ColumnList, ByVal schema As DataTable, ByVal doc As Xml.XmlDocument) As Xml.XmlNode
        Try
            Select Case e.GetType().ToString
                Case GetType(PredicateExpression).ToString
                    Dim obj As PredicateExpression = CType(e, PredicateExpression)
                    Dim n As Xml.XmlElement = Nothing
                    Select Case obj.Op
                        Case Operators.Equals
                            n = doc.CreateElement("Eq")
                        Case Operators.GreaterThan
                            n = doc.CreateElement("Gt")
                    End Select

                    Dim fieldRef As XmlElement = doc.CreateElement("FieldRef")
                    Dim attribute As XmlAttribute = doc.CreateAttribute("Name")

                    Dim column As ColumnDefinition = Nothing
                    For Each c As ColumnDefinition In columns
                        If c.ColumnName.GetFullName() = obj.Left Then
                            column = c
                            Exit For
                        End If
                    Next

                    If column IsNot Nothing Then
                        attribute.Value = column.ColumnName.Name
                    Else
                        attribute.Value = obj.Left
                    End If

                    ''Hack
                    fieldRef.Attributes.Append(attribute)
                    Dim Value As XmlElement = doc.CreateElement("Value")
                    Dim TypeAttr As XmlAttribute = doc.CreateAttribute("Type")

                    TypeAttr.Value = ToSPFieldType(schema.Columns.Item(column.ColumnAlias).DataType)
                    Value.Attributes.Append(TypeAttr)

                    'Right hand side must contain a value type
                    If obj.Right.StartsWith("@") Then
                        Value.InnerText = CType(Me._Parameters.Item(obj.Right), DataParameter).Value
                    Else
                        Value.InnerText = obj.Right
                    End If


                    n.AppendChild(fieldRef)
                    n.AppendChild(Value)
                    Return n
                Case GetType(LogicalExpression).ToString
                    Dim obj As LogicalExpression = CType(e, LogicalExpression)
                    Dim n As Xml.XmlElement = Nothing
                    Select Case obj.Op
                        Case BooleanOperators.Or
                            n = doc.CreateElement("Or")
                            n.AppendChild(Me.ExpressionToCAML(obj.Left, columns, schema, doc))
                            n.AppendChild(Me.ExpressionToCAML(obj.Right, columns, schema, doc))
                        Case BooleanOperators.And
                            n = doc.CreateElement("And")
                            n.AppendChild(Me.ExpressionToCAML(obj.Left, columns, schema, doc))
                            n.AppendChild(Me.ExpressionToCAML(obj.Right, columns, schema, doc))
                        Case BooleanOperators.None
                            n = Me.ExpressionToCAML(obj.Left, columns, schema, doc)
                    End Select
                    Return n
                Case Else
                    Throw New Exception("FAIL")
            End Select
        Catch ex As Exception
            Throw
        End Try

    End Function

    Private Function TypeFromField(ByVal field As XmlElement) As System.Type

        Select Case field.GetAttribute("Type")
            Case "DateTime"
                Return GetType(DateTime)
            Case "Integer"
                Return GetType(Integer)
            Case "Number"
                Return GetType(Decimal)
            Case Else
                Return GetType(String)
        End Select

    End Function

    Public Function ExecuteScalar() As Object Implements System.Data.IDbCommand.ExecuteScalar
        Throw New NotImplementedException()
    End Function

    Public ReadOnly Property Parameters() As System.Data.IDataParameterCollection Implements System.Data.IDbCommand.Parameters
        Get
            Return _Parameters
        End Get
    End Property

    Public Sub Prepare() Implements System.Data.IDbCommand.Prepare
        'noop
    End Sub

    Public Property Transaction() As System.Data.IDbTransaction Implements System.Data.IDbCommand.Transaction
        Get
            Return _Transaction
        End Get
        Set(ByVal value As System.Data.IDbTransaction)
            _Transaction = value
        End Set
    End Property


    Public Property UpdatedRowSource() As System.Data.UpdateRowSource Implements System.Data.IDbCommand.UpdatedRowSource
        Get
            Return UpdateRowSource.None
        End Get
        Set(ByVal value As System.Data.UpdateRowSource)

        End Set
    End Property

    Private disposedValue As Boolean = False        ' To detect redundant calls

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                ' TODO: free managed resources when explicitly called
            End If

            ' TODO: free shared unmanaged resources
        End If
        Me.disposedValue = True
    End Sub

#Region " IDisposable Support "
    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region


    Private Shared _nsmgr As XmlNamespaceManager
    Private Shared ReadOnly Property NameSpaceMgr() As XmlNamespaceManager
        Get
            If _nsmgr Is Nothing Then
                _nsmgr = New XmlNamespaceManager(New NameTable())
                _nsmgr.AddNamespace("wss", "http://schemas.microsoft.com/sharepoint/soap/")
                _nsmgr.AddNamespace("s", "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882")
                _nsmgr.AddNamespace("dt", "uuid:C2F41010-65B3-11d1-A29F-00AA00C14882")
                _nsmgr.AddNamespace("rs", "urn:schemas-microsoft-com:rowset")
                _nsmgr.AddNamespace("z", "#RowsetSchema")
            End If
            Return _nsmgr
        End Get
    End Property

    Public Sub New()
        _Parameters = New DataParameterCollection
    End Sub
End Class
